USE recipeshop
/*--------------------------------------------------
dbo.Category_Recipe
----------------------------------------------------*/
GO
-- Get all Category Recipe
CREATE PROC proc_GET_ALL_CATEGORYRECIPE
AS
	BEGIN
		SELECT * FROM [CATEGORY_RECIPE]
		WHERE [STATUS] = 1
	END
GO
-- Get Category Recipe by id
CREATE PROC proc_GET_CATEGORYRECIPE_BY_ID
(
	@ID INT
)
AS
	BEGIN
		SELECT [NAME],[IMAGE],[STATUS] 
		FROM [CATEGORY_RECIPE]
		WHERE [ID] = @ID 
		AND [STATUS] = 1
	END
GO
-- Add Category_Recipe
CREATE PROC proc_ADD_CATEGORYRECIPE
(
	@NAME NVARCHAR(50),
	@IMAGE VARCHAR(50),
	@STATUS INT
)
AS 
	BEGIN
		INSERT INTO [CATEGORY_RECIPE]([NAME],[IMAGE],[STATUS]) 
		VALUES (@NAME,@IMAGE,@STATUS)
	END
GO
-- Update Category Recipe
CREATE PROC proc_UPDATE_CATEGORYRECIPE
(
	@ID INT,
	@NAME NVARCHAR(50),
	@IMAGE VARCHAR(50),
	@STATUS INT
)
AS 
	BEGIN
		UPDATE [CATEGORY_RECIPE] 
		SET [NAME] = @NAME,[IMAGE] = @IMAGE, [STATUS] = @STATUS
		WHERE [ID] = @ID
	END
GO
-- Delete Category Recipe
CREATE PROC proc_DELETE_CATEGORYRECIPE
(
	@ID INT
)
AS
	BEGIN
		DELETE [CATEGORY_RECIPE] WHERE [ID] = @ID
		DELETE [LIST_RECIPE] WHERE [ID_CATE] = @ID
	END
GO
/*--------------------------------------------------
dbo.FAQ
----------------------------------------------------*/
-- Get all FAQ
CREATE PROC proc_GET_ALL_FAQ
AS
	BEGIN
		SELECT * FROM [FAQ]
		WHERE [STATUS] = 1
	END
GO
-- Get FAQ by id
CREATE PROC proc_GET_FAQ_BY_ID
(
	@ID INT
)
AS
	BEGIN
		SELECT [ID_USER],[DESCRIPTION],[ANSWER],[STATUS] 
		FROM [FAQ]
		WHERE [ID] = @ID
		AND [STATUS] = 1
	END
GO
-- Add FAQ
CREATE PROC proc_ADD_FAQ
(
	@ID_USER INT,
	@DESCRIPTION TEXT,
	@ANSWER TEXT,
	@STATUS INT
)
AS 
	BEGIN
		INSERT INTO [FAQ]([ID_USER],[DESCRIPTION],[ANSWER],[STATUS]) 
		VALUES (@ID_USER,@DESCRIPTION,@ANSWER,@STATUS)
	END
GO
-- Update FAQ
CREATE PROC proc_UPDATE_FAQ
(
	@ID INT,
	@ID_USER INT,
	@DESCRIPTION TEXT,
	@ANSWER TEXT,
	@STATUS INT
)
AS 
	BEGIN
		UPDATE [FAQ] 
		SET [ID_USER] = @ID_USER,[DESCRIPTION] = @DESCRIPTION,[ANSWER] = @ANSWER,[STATUS] = @STATUS
		WHERE [ID] = @ID
	END
GO
-- Delete FAQ
CREATE PROC proc_DELETE_FAQ
(
	@ID INT
)
AS
	BEGIN
		DELETE [FAQ] 
		WHERE [ID] = @ID
	END
GO
/*--------------------------------------------------
dbo.Feed_Back
----------------------------------------------------*/
-- Get all Feedback
CREATE PROC proc_GET_ALL_FEEDBACK
AS
	BEGIN
		SELECT * FROM [FEED_BACK]
		WHERE [STATUS] = 1
	END
GO
-- Get Feedback by id
CREATE PROC proc_GET_FEEDBACK_BY_ID
(
	@ID INT
)
AS
	BEGIN
		SELECT [TITLE],[DESCRIPTION],[DATE],[EMAIL],[STATUS] 
		FROM [FEED_BACK]
		WHERE [ID] = @ID
		AND [STATUS] = 1
	END
GO
-- Add Feedback
CREATE PROC proc_ADD_FEEDBACK
(
	@TITLE NVARCHAR(50),
	@DESCRIPTION TEXT,
	@DATE DATETIME,
	@EMAIL NVARCHAR(5),
	@STATUS INT
)
AS 
	BEGIN
		INSERT INTO [FEED_BACK]([TITLE],[DESCRIPTION],[DATE],[EMAIL],[STATUS]) 
		VALUES (@TITLE,@DESCRIPTION,@DATE,@EMAIL,@STATUS)
	END
GO
-- Update Feedback
CREATE PROC proc_UPDATE_FEEDBACK
(
	@ID INT,
	@TITLE NVARCHAR(50),
	@DESCRIPTION TEXT,
	@DATE DATETIME,
	@EMAIL NVARCHAR(5),
	@STATUS INT
)
AS 
	BEGIN
		UPDATE [FEED_BACK] 
		SET [TITLE] = @TITLE,[DESCRIPTION] = @DESCRIPTION,[DATE] = @DATE,[EMAIL] = @EMAIL,[STATUS] = @STATUS
		WHERE [ID] = @ID
	END
GO
-- Delete Feedback
CREATE PROC proc_DELETE_FEEDBACK
(
	@ID INT
)
AS
	BEGIN
		DELETE [FEED_BACK] 
		WHERE [ID] = @ID
	END
GO
/*--------------------------------------------------
dbo.Information
----------------------------------------------------*/
-- Get all Information
CREATE PROC proc_GET_ALL_INFORMATION
AS
	BEGIN
		SELECT * FROM [INFORMATION]
		WHERE [STATUS] = 1
	END
GO
-- Get Information by id
CREATE PROC proc_GET_INFORMATION_BY_ID
(
	@ID INT
)
AS
	BEGIN
		SELECT [NAME],[DESCRIPTION],[STATUS] 
		FROM [INFORMATION]
		WHERE [ID] = @ID
		AND [STATUS] = 1
	END
GO
-- Add Information
CREATE PROC proc_ADD_INFORMATION
(
	@NAME NVARCHAR(50),
	@DESCRIPTION TEXT,
	@STATUS INT
)
AS 
	BEGIN
		INSERT INTO [INFORMATION]([NAME],[DESCRIPTION],[STATUS]) 
		VALUES (@NAME,@DESCRIPTION,@STATUS)
	END
GO
-- Update Information
CREATE PROC proc_UPDATE_INFORMATION
(
	@ID INT,
	@NAME NVARCHAR(50),
	@DESCRIPTION TEXT,
	@STATUS INT
)
AS 
	BEGIN
		UPDATE [INFORMATION] 
		SET [NAME] = @NAME,[DESCRIPTION] = @DESCRIPTION,[STATUS] = @STATUS
		WHERE [ID] = @ID
	END
GO
-- Delete Information
CREATE PROC proc_DELETE_INFORMATION
(
	@ID INT
)
AS
	BEGIN
		DELETE [INFORMATION] 
		WHERE [ID] = @ID
	END
GO
/*--------------------------------------------------
dbo.List_Recipe
----------------------------------------------------*/
-- Get all Recipe
CREATE PROC proc_GET_ALL_LISTRECIPE
AS
	BEGIN
		SELECT * FROM [LIST_RECIPE]
		WHERE [STATUS] = 1
	END
GO
-- Get Recipe by id
CREATE PROC proc_GET_LISTRECIPE_BY_ID
(
	@ID INT
)
AS
	BEGIN
		SELECT [ID_CATE],[TITLE],[IMAGE],[DESCRIPTION],[DETAILS],[PRICE],[DISCOUNT],[STATUS] 
		FROM [LIST_RECIPE]
		WHERE [ID] = @ID
		AND [STATUS] = 1
	END
GO
-- Add Recipe
CREATE PROC proc_ADD_LISTRECIPE
(
	@ID_CATE INT,
	@TITLE NVARCHAR(50),
	@IMAGE NVARCHAR(100),
	@DESCRIPTION TEXT,
	@DETAILS TEXT,
	@PRICE FLOAT,
	@DISCOUNT FLOAT,
	@STATUS INT
)
AS 
	BEGIN
		INSERT INTO [LIST_RECIPE]([ID_CATE],[TITLE],[IMAGE],[DESCRIPTION],[DETAILS],[PRICE],[DISCOUNT],[STATUS]) 
		VALUES (@ID_CATE,@TITLE,@IMAGE,@DESCRIPTION,@DETAILS,@PRICE,@DISCOUNT,@STATUS)
	END
GO
-- Update Recipe
CREATE PROC proc_UPDATE_LISTRECIPE
(
	@ID INT,
	@ID_CATE INT,
	@TITLE NVARCHAR(50),
	@IMAGE NVARCHAR(100),
	@DESCRIPTION TEXT,
	@DETAILS TEXT,
	@PRICE FLOAT,
	@DISCOUNT FLOAT,
	@STATUS INT
)
AS 
	BEGIN
		UPDATE [LIST_RECIPE] 
		SET [ID_CATE] = @ID_CATE,[TITLE] = @TITLE,[IMAGE] = @IMAGE,[DESCRIPTION] = @DESCRIPTION,[DETAILS] = @DETAILS,[PRICE] = @PRICE,[DISCOUNT] = @DISCOUNT,[STATUS] = @STATUS
		WHERE [ID] = @ID
	END
GO
-- Delete Recipe
CREATE PROC proc_DELETE_LISTRECIPE
(
	@ID INT
)
AS
	BEGIN
		DELETE [LIST_RECIPE] 
		WHERE [ID] = @ID
	END
GO
/*--------------------------------------------------
dbo.Order
----------------------------------------------------*/
-- Get all Order
CREATE PROC proc_GET_ALL_ORDER
AS
	BEGIN
		SELECT * FROM [ORDER]
		WHERE [STATUS] = 1
	END
GO
-- Get Order by id
CREATE PROC proc_GET_ORDER_BY_ID
(
	@ID INT
)
AS
	BEGIN
		SELECT [ID_USER],[ID_ORDER_DETAILS],[ID_PAYMENT],[STATUS] FROM [ORDER]
		WHERE [ID] = @ID
		AND [STATUS] = 1
	END
GO
-- Add Order
CREATE PROC proc_ADD_ORDER
(
	@ID_USER INT,
	@ID_ORDER_DETAILS INT,
	@ID_PAYMENT INT,
	@STATUS INT
)
AS 
	BEGIN
		INSERT INTO [ORDER]([ID_USER],[ID_ORDER_DETAILS],[ID_PAYMENT],[STATUS])
		VALUES (@ID_USER,@ID_ORDER_DETAILS,@ID_PAYMENT,@STATUS)
	END
GO
-- Update Order
CREATE PROC proc_UPDATE_ORDER
(
	@ID INT,
	@ID_USER INT,
	@ID_ORDER_DETAILS INT,
	@ID_PAYMENT INT,
	@STATUS INT
)
AS 
	BEGIN
		UPDATE [ORDER] 
		SET [ID_USER] = @ID_USER,[ID_ORDER_DETAILS] = @ID_ORDER_DETAILS,[ID_PAYMENT] = @ID_PAYMENT,[STATUS] = @STATUS
		WHERE [ID] = @ID
	END
GO
-- Delete Order
CREATE PROC proc_DELETE_ORDER
(
	@ID INT
)
AS
	BEGIN
		DELETE [ORDER] WHERE [ID] = @ID
	END
GO
/*--------------------------------------------------
dbo.Order_Detail
----------------------------------------------------*/
-- Get all Order_Detail
CREATE PROC proc_GET_ALL_ORDERDETAIL
AS
	BEGIN
		SELECT * FROM [ORDER_DETAIL]
		WHERE [STATUS] = 1
	END
GO
-- Get Order_Detail by id
CREATE PROC proc_GET_ORDERDETAIL_BY_ID
(
	@ID INT
)
AS
	BEGIN
		SELECT [ID_RECIPE],[STATUS] FROM [ORDER_DETAIL]
		WHERE [ID] = @ID
		AND [STATUS] = 1
	END
GO
-- Add Order_Detail
CREATE PROC proc_ADD_ORDERDETAIL
(
	@ID_RECIPE INT,
	@STATUS INT
)
AS 
	BEGIN
		INSERT INTO [ORDER_DETAIL]([ID_RECIPE],[STATUS])
		VALUES (@ID_RECIPE,@STATUS)
	END
GO
-- Update Order_Detail
CREATE PROC proc_UPDATE_ORDERDETAIL
(
	@ID INT,
	@ID_RECIPE INT,
	@STATUS INT
)
AS 
	BEGIN
		UPDATE [ORDER_DETAIL] 
		SET [ID_RECIPE] = @ID_RECIPE,[STATUS] = @STATUS
		WHERE [ID] = @ID
	END
GO
-- Delete Order_Detail
CREATE PROC proc_DELETE_ORDERDETAIL
(
	@ID INT
)
AS
	BEGIN
		DELETE [ORDER_DETAIL] WHERE [ID] = @ID
	END
GO
/*--------------------------------------------------
dbo.Payment
----------------------------------------------------*/
-- Get all Payment
CREATE PROC proc_GET_ALL_PAYMENT
AS
	BEGIN
		SELECT * FROM [PAYMENT]
		WHERE [STATUS] = 1
	END
GO
-- Get Payment by id
CREATE PROC proc_GET_PAYMENT_BY_ID
(
	@ID INT
)
AS
	BEGIN
		SELECT [NAME],[STATUS] FROM [PAYMENT]
		WHERE [ID] = @ID
		AND [STATUS] = 1
	END
GO
-- Add Payment
CREATE PROC proc_ADD_PAYMENT
(
	@NAME NVARCHAR(50),
	@STATUS INT
)
AS 
	BEGIN
		INSERT INTO [PAYMENT]([NAME],[STATUS]) VALUES (@NAME,@STATUS)
	END
GO
-- Update Payment
CREATE PROC proc_UPDATE_PAYMENT
(
	@ID INT,
	@NAME NVARCHAR(50),
	@STATUS INT
)
AS 
	BEGIN
		UPDATE [PAYMENT] SET [NAME] = @NAME,[STATUS] = @STATUS
		WHERE [ID] = @ID
	END
GO
-- Delete Payment
CREATE PROC proc_DELETE_PAYMENT
(
	@ID INT
)
AS
	BEGIN
		DELETE [PAYMENT] WHERE [ID] = @ID
	END
GO
/*--------------------------------------------------
dbo.Role
----------------------------------------------------*/
-- Get all Role
CREATE PROC proc_GET_ALL_ROLE
AS
	BEGIN
		SELECT * FROM [ROLE]
		WHERE [STATUS] = 1
	END
GO
-- Get Role by id
CREATE PROC proc_GET_ROLE_BY_ID
(
	@ID_ROLE INT
)
AS
	BEGIN
		SELECT [ID_USER_ROLE],[STATUS] FROM [ROLE]
		WHERE [ID_ROLE] = @ID_ROLE
		AND [STATUS] = 1
	END
GO
-- Add Role
CREATE PROC proc_ADD_ROLE
(
	@ID_ROLE INT,
	@ID_USER_ROLE INT,
	@STATUS INT
)
AS 
	BEGIN
		INSERT INTO [ROLE]([ID_ROLE],[ID_USER_ROLE],[STATUS])
		VALUES (@ID_ROLE,@ID_USER_ROLE,@STATUS)
	END
GO
-- Update Role
CREATE PROC proc_UPDATE_ROLE
(
	@ID_ROLE INT,
	@ID_USER_ROLE INT,
	@STATUS INT
)
AS 
	BEGIN
		UPDATE [ROLE] 
		SET [ID_USER_ROLE] = @ID_USER_ROLE,[STATUS] = @STATUS
		WHERE [ID_ROLE] = @ID_ROLE
	END
GO
-- Delete Role
CREATE PROC proc_DELETE_ROLE
(
	@ID_ROLE INT
)
AS
	BEGIN
		DELETE [ROLE] WHERE [ID_ROLE] = @ID_ROLE
		DELETE [ROLE_DETAIL] WHERE [ID_ROLE] = @ID_ROLE
	END
GO
/*--------------------------------------------------
dbo.Role_Detail
----------------------------------------------------*/
-- Get all Role_Detail
CREATE PROC proc_GET_ALL_ROLEDETAIL
AS
	BEGIN
		SELECT * FROM [ROLE_DETAIL]
		WHERE [STATUS] = 1
	END
GO
-- Get Role_Detail by id
CREATE PROC proc_GET_ROLEDETAIL_BY_ID
(
	@ID_ROLE INT
)
AS
	BEGIN
		SELECT [DETAIL],[STATUS] FROM [ROLE_DETAIL]
		WHERE [ID_ROLE] = @ID_ROLE
		AND [STATUS] = 1
	END
GO
-- Add Role_Detail
CREATE PROC proc_ADD_ROLEDETAIL
(
	@DETAIL NVARCHAR(1000),
	@STATUS INT
)
AS 
	BEGIN
		INSERT INTO [ROLE_DETAIL]([DETAIL],[STATUS])
		VALUES (@DETAIL,@STATUS)
	END
GO
-- Update Role_Detail
CREATE PROC proc_UPDATE_ROLEDETAIL
(
	@ID_ROLE INT,
	@DETAIL NVARCHAR(1000),
	@STATUS INT
)
AS 
	BEGIN
		UPDATE [ROLE_DETAIL] 
		SET [DETAIL] = @DETAIL,[STATUS] = @STATUS
		WHERE [ID_ROLE] = @ID_ROLE
	END
GO
-- Delete Role_Detail
CREATE PROC proc_DELETE_ROLEDETAIL
(
	@ID_ROLE INT
)
AS
	BEGIN
		DELETE [ROLE_DETAIL] WHERE [ID_ROLE] = @ID_ROLE
	END
GO
/*--------------------------------------------------
dbo.User
----------------------------------------------------*/
-- Get all User
CREATE PROC proc_GET_ALL_USER
AS
	BEGIN
		SELECT * FROM [USER]
		WHERE [STATUS] = 1
	END
GO
-- Get User by id
CREATE PROC proc_GET_USER_BY_ID
(
	@ID INT
)
AS
	BEGIN
		SELECT [USERNAME],[PASSWORD],[FIRSTNAME],[LASTNAME],[PHONE],[EMAIL],[ADDRESS],[ID_ROLE],[STATUS] FROM [USER]
		WHERE [ID] = @ID
		AND [STATUS] = 1
	END
GO
-- Add User
CREATE PROC proc_ADD_USER
(
	@USERNAME NVARCHAR(50),
	@PASSWORD NVARCHAR(500),
	@FIRSTNAME NVARCHAR(50),
	@LASTNAME NVARCHAR(50),
	@PHONE VARCHAR(50),
	@EMAIL NVARCHAR(50),
	@ADDRESS NVARCHAR(500),
	@ID_ROLE INT,
	@STATUS INT
)
AS 
	BEGIN
		INSERT INTO [USER]([USERNAME],[PASSWORD],[FIRSTNAME],[LASTNAME],[PHONE],[EMAIL],[ADDRESS],[ID_ROLE],[STATUS])
		VALUES (@USERNAME,@PASSWORD,@FIRSTNAME,@LASTNAME,@PHONE,@EMAIL,@ADDRESS,@ID_ROLE,@STATUS)
	END
GO
-- Update User
CREATE PROC proc_UPDATE_USER
(
	@ID INT,
	@USERNAME NVARCHAR(50),
	@PASSWORD NVARCHAR(500),
	@FIRSTNAME NVARCHAR(50),
	@LASTNAME NVARCHAR(50),
	@PHONE VARCHAR(50),
	@EMAIL NVARCHAR(50),
	@ADDRESS NVARCHAR(500),
	@ID_ROLE INT,
	@STATUS INT
)
AS 
	BEGIN
		UPDATE [USER] 
		SET [USERNAME] = @USERNAME,[PASSWORD] = @PASSWORD,[FIRSTNAME] = @FIRSTNAME,[LASTNAME] = @LASTNAME,
			[PHONE] = @PHONE,[EMAIL] = @EMAIL,[ADDRESS] = @ADDRESS,[ID_ROLE] = @ID_ROLE,[STATUS] = @STATUS
		WHERE [ID] = @ID
	END
GO
-- Delete User
CREATE PROC proc_DELETE_USER
(
	@ID INT,
	@ID_USER INT
)
AS
	BEGIN
		DELETE [USER] WHERE [ID] = @ID
		DELETE [ORDER] WHERE [ID_USER] = @ID_USER
	END
GO
/*--------------------------------------------------
dbo.UserRole
----------------------------------------------------*/
-- Get all UserRole
CREATE PROC proc_GET_ALL_USERROLE
AS
	BEGIN
		SELECT * FROM [USERROLE]
		WHERE [STATUS] = 1
	END
GO
-- Get UserRole by id
CREATE PROC proc_GET_USERROLE_BY_ID
(
	@ID INT
)
AS
	BEGIN
		SELECT [NAME],[STATUS] 
		FROM [USERROLE]
		WHERE [ID] = @ID
		AND [STATUS] = 1
	END
GO
-- Add UserRole
CREATE PROC proc_ADD_USERROLE
(
	@NAME NVARCHAR(100),
	@STATUS INT
)
AS 
	BEGIN
		INSERT INTO [USERROLE]([NAME],[STATUS]) VALUES (@NAME,@STATUS)
	END
GO
-- Update UserRole
CREATE PROC proc_UPDATE_USERROLE
(
	@ID INT,
	@NAME NVARCHAR(100),
	@STATUS INT
)
AS 
	BEGIN
		UPDATE [USERROLE] SET [NAME] = @NAME,[STATUS] = @STATUS
		WHERE [ID] = @ID
	END
GO
-- Delete UserRole
CREATE PROC proc_DELETE_USERROLE
(
	@ID INT
)
AS
	BEGIN
		DELETE [USERROLE] WHERE [ID] = @ID
	END
GO